"""
Case Type   : 基础功能
Case Name   : 创建数据库使用不同兼容模式
Create At   : 2024/10/15
Owner       : cr13
Description :
    1.查询默认值
    2.修改参数值为on
    3.重启数据库
    4.查询修改后的参数值
    5.创建兼容oracle数据库
    6.查询数据
    7.创建数据库兼容mysql
    8.查询数据
    9.创建数据库兼容TD
    10.查询数据
    11.创建数据库兼容PG
    12.查询数据
    13.查看数据库默认所有者
Expect      :
    1.查询默认值成功
    2.成功修改参数
    3.成功重启
    4.修改后为on
    5.创建数据库成功
    6.查询数据成功
    7.创建数据库兼容mysql成功
    8.查询数据成功
    9.创建数据库兼容TD成功
    10.查询数据成功
    11.创建数据库兼容PG成功
    12.查询数据成功
    13.查看数据库默认所有者成功
History     :
"""


import unittest
import os
from yat.test import Node

from testcase.utils.CommonSH import CommonSH
from testcase.utils.Constant import Constant
from testcase.utils.Logger import Logger

LOG = Logger()


class DdlDatabase(unittest.TestCase):

    def setUp(self):
        self.log = Logger()
        self.commonsh = CommonSH('PrimaryDbUser')
        self.log.info('--Opengauss_Function_Float_Case0003.py start--')
        self.db_primary_db_user = Node(node='PrimaryDbUser')
        self.constant = Constant()
        self.dbname = 'database_case003'

    def test_basebackup(self):
        LOG.info('---步骤1:查询默认值---')
        sql_cmd = self.commonsh.execut_db_sql('''show float_suffix_acceptance;''')
        LOG.info(sql_cmd)
        self.res = sql_cmd.splitlines()[-2].strip()
        LOG.info('---步骤2:修改参数值为on---')
        msg = self.commonsh.execute_gsguc('set',
                                     self.constant.GSGUC_SUCCESS_MSG,
                                     "float_suffix_acceptance = on")
        LOG.info(msg)
        self.assertTrue(msg)
        LOG.info('---步骤3:重启数据库---')
        msg = self.commonsh.restart_db_cluster()
        LOG.info(msg)
        status = self.commonsh.get_db_cluster_status()
        self.assertTrue("Degraded" in status or "Normal" in status)
        LOG.info('---步骤4:查询修改后的参数值---')
        sql_cmd = self.commonsh.execut_db_sql('''show float_suffix_acceptance;''')
        LOG.info(sql_cmd)
        self.assertIn(self.constant.BOOLEAN_VALUES[0], sql_cmd)

        self.log.info('------5.创建兼容oracle数据库-------')
        sql = f"drop database if exists {self.dbname};\
        create database {self.dbname} with DBCOMPATIBILITY ='A'  " \
            f"TEMPLATE=template0 ENCODING='UTF8';"
        result = self.commonsh.execut_db_sql(sql)
        self.log.info(result)
        self.assertIn(self.constant.CREATE_DATABASE_SUCCESS, result)

        self.log.info('------6.查询数据----')
        sql = f'select 3.14fd;'
        result = self.commonsh.execut_db_sql(sql, dbname=self.dbname)
        self.log.info(result)
        self.assertEqual(str(result.splitlines()[-2]).strip(), '3.14',
                         "执行失败")
        self.assertEqual(str(result.splitlines()[-4]).strip(), 'd',
                         "执行失败")


        self.log.info('---------7.创建数据库兼容mysql------')
        sql = f"drop database if exists {self.dbname};" \
            f"create database {self.dbname} with" \
            f" DBCOMPATIBILITY ='B'" \
            f" TEMPLATE=template0 ENCODING='UTF8'"
        result = self.commonsh.execut_db_sql(sql)
        self.log.info(result)
        self.assertIn(self.constant.CREATE_DATABASE_SUCCESS, result)

        self.log.info('------8.查询数据----')
        sql = f'select 3.14fd;'
        result = self.commonsh.execut_db_sql(sql, dbname=self.dbname)
        self.log.info(result)
        self.assertEqual(str(result.splitlines()[-2]).strip(), '3.14',
                         "执行失败")
        self.assertEqual(str(result.splitlines()[-4]).strip(), 'fd',
                         "执行失败")

        self.log.info('---------9.创建数据库兼容TD------')
        sql = f"drop database if exists {self.dbname};" \
            f"create database {self.dbname} with DBCOMPATIBILITY ='C' " \
            f"TEMPLATE=template0 ENCODING='UTF8'"
        result = self.commonsh.execut_db_sql(sql)
        self.log.info(result)
        self.assertIn(self.constant.CREATE_DATABASE_SUCCESS, result)

        self.log.info('------10.查询数据----')
        sql = f'select 3.14fd;'
        result = self.commonsh.execut_db_sql(sql, dbname=self.dbname)
        self.log.info(result)
        self.assertEqual(str(result.splitlines()[-2]).strip(), '3.14',
                         "执行失败")
        self.assertEqual(str(result.splitlines()[-4]).strip(), 'fd',
                         "执行失败")

        self.log.info('--------11.创建数据库兼容PG------')
        sql = f"drop database if exists {self.dbname};" \
            f"create database {self.dbname} with " \
            f"DBCOMPATIBILITY ='PG' TEMPLATE=template0 ENCODING='UTF8'"
        result = self.commonsh.execut_db_sql(sql)
        self.log.info(result)
        self.assertIn(self.constant.CREATE_DATABASE_SUCCESS, result)

        self.log.info('------12.查询数据----')
        sql = f'select 3.14fd;'
        result = self.commonsh.execut_db_sql(sql, dbname=self.dbname)
        self.log.info(result)
        self.assertEqual(str(result.splitlines()[-2]).strip(), '3.14',
                         "执行失败")
        self.assertEqual(str(result.splitlines()[-4]).strip(), 'fd',
                         "执行失败")

        self.log.info('----------13.查看数据库默认所有者-------')
        result = self.commonsh.execut_db_sql(f'\l')
        self.log.info(result)
        db_lists = result.strip().splitlines()
        flag = True
        for i in db_lists:
            if self.dbname in i:
                db_info = i.strip().split('|')[1]
                self.assertIn(self.db_primary_db_user.ssh_user, db_info)
                flag = False
                break
        self.assertFalse(flag)



    def tearDown(self):
        self.log.info('------------环境清理-----------')
        self.log.info('---------删除数据库------')
        result = self.commonsh.execut_db_sql(
            f"drop database if exists {self.dbname};")
        self.log.info(result)
        self.assertIn(self.constant.DROP_DATABASE_SUCCESS, result)
        self.log.info('--Opengauss_Function_Float_Case0003.py finish-')

